﻿using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Sustainalytics.Entities.RiskRatings;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Xml.Linq;
using Sustainalytics.Utils;
using MongoDB.Driver;
using System.Threading.Tasks;

namespace Sustainalytics.ClientDW.Synchronization.SyncRiskRatingsData.Events
{
 

    public class EventsQuery 
    {
        private readonly int _commandTimeout;
        private readonly SqlDatabase _database;
        private readonly IMongoCollection<EventMapping> _mappingCollection;

        public EventsQuery(SqlDatabase database, IMongoCollection<EventMapping> mappingCollection)
        {
            _database = database ?? throw new Exception("Sql Connection string must be provided!");
            _commandTimeout = 3600;
            _mappingCollection = mappingCollection;
        }

        public async Task<Dictionary<string,EventMapping>> GetMapping()
        {
            return (await _mappingCollection.Find(_ => true).ToListAsync().ConfigureAwait(false)).ToDictionary(x => x.Id);
        }

        private Dictionary<Guid, List<string>> GetEventIndicatorOutlookComments(List<Guid> companyIds)
        {
            var eventIndicatorComments = new Dictionary<Guid, List<string>>();

            using (var reader = GetEventIndicatorOutlookCommentsReader(companyIds))
            {
                if (reader == null) return eventIndicatorComments;

                var currentCommentId = Guid.Empty;
                var currentCommentTexts = new List<string>();

                while (reader.Read())
                {
                    var commentId = (Guid)reader["CommentId"];

                    if (currentCommentId != commentId)
                    {
                        currentCommentId = commentId;

                        currentCommentTexts = new List<string>();

                        eventIndicatorComments.Add(currentCommentId, currentCommentTexts);
                    }

                    currentCommentTexts.Add((string)reader["Comment"]);
                }
            }

            return eventIndicatorComments;
        }

        public IEnumerable<EventIndicator> Get(List<Guid> companyIds)
        {
            var eventIndicatorOutlookComments = GetEventIndicatorOutlookComments(companyIds);
            var eventIndicators = new List<EventIndicator>();
            
            using (var reader = GetEventIndicatorsReader(companyIds))
            {
                if (reader == null) return eventIndicators;

                while (reader.Read())
                {
                    var outlook = reader["OutlookScore"] as int?;
                    var outlookCommentsId = reader["OutlookCommentsId"] as Guid?;

                    eventIndicators.Add(new EventIndicator
                    {
                        Id = (Guid)reader["EventIndicatorId"],
                        Name = (string)reader["EventIndicatorName"],
                        EventCode = (string)reader["EventIndicatorCode"],
                        CompanyId = (Guid)reader["CompanyId"],
                        ControversySummary = reader["ControversySummary"] as string,
                        EventCategory = (int)reader["AnswerCategoryId"],
                        OutlookText = reader["OutlookText"] as string,
                        Outlook = outlook,
                        EventJustification = reader["AnswerCategoryJustification"] as string,
                        EventCategoryText = ScoreConversion.GetEventSeverity((int)reader["AnswerCategoryId"]),
                        OutlookLabel = outlook.HasValue ? ScoreConversion.GetOutlookAssessment(outlook.Value) : null,
                        OutlookComments = outlookCommentsId.HasValue ? eventIndicatorOutlookComments.TryGetValue(outlookCommentsId.Value, out var oc) ? oc : new List<string>()
                                                                        : null
                    });
                }
            }

            return eventIndicators;
        }

        private IDataReader GetEventIndicatorOutlookCommentsReader(List<Guid> companyIds)
        {
            var command = new SqlCommand
            {
                CommandText = "spGetEventIndicatorOutlookCommentsByCompanyIds",
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = _commandTimeout
            };

            var ids = new XElement("CompanyIds", companyIds.Select(id => new XElement("Id", id)));

            command.Parameters.AddWithValue("@CompanyIds", ids.ToString());

            return _database.ExecuteReader(command);
        }

        private IDataReader GetEventIndicatorsReader(List<Guid> companyIds)
        {
            var command = new SqlCommand
            {
                CommandText = "spGetEventIndicatorsByCompanyIds",
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = _commandTimeout
            };

            var ids = new XElement("CompanyIds", companyIds.Select(id => new XElement("Id", id)));

            command.Parameters.AddWithValue("@CompanyIds", ids.ToString());

            return _database.ExecuteReader(command);
        }
    }
}